package com.deng.etl.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.Date;

import com.deng.etl.common.IEtlInterface;
import com.deng.etl.common.EtlLog;

//删除

public class ImportFeeItems extends ImportBase implements IEtlInterface {
	
	public static String COLUMNS="item_code,item_name,unit_price";
	
	public static String SELECT="select "+COLUMNS+"  from neusoft.JIXIAO_FEE_ITEMS";
	
	public static String INSERT="insert into UNIT_PRICE_ITEM_WEIGHT" +
			"(item_id,item_type,order_weight,operate_weight,price,item_name,item_type_name,point," +
			"create_time,modify_time) values(?,?,?,?,?,?,?,?,?,?)";
	
	public static String UPDATE="update UNIT_PRICE_ITEM_WEIGHT set item_type=?,order_weight=?,operate_weight=?," +
			"price =?,item_name=?,item_type_name=?,point=?,create_time=?,modify_time=?" +
			"where item_id=?";	
	
	public static String DELETE="delete from UNIT_PRICE_ITEM_WEIGHT";
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		
	}
	
	public boolean checkExist(Connection conn,String code){
		boolean ret=false;
		try {
			System.out.println("---------checkExist-------"+code);
			PreparedStatement pst =conn.prepareStatement("select item_id from UNIT_PRICE_ITEM_WEIGHT where item_id='"+code+"'");
			ResultSet rs=pst.executeQuery();
			if(rs.next()){
				ret=true;
			}
			rs.close();
			pst.close();
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ret;
	}
	
	
	public void insertData(Connection sourceConn,Connection targetConn,String name,String fromDate,String toDate) {
		System.out.println("从："+fromDate+"   到："+toDate);
		String index="0";
		try {
			
			PreparedStatement pst=sourceConn.prepareStatement(SELECT+" where item_code>? " +
					"order by item_code FETCH FIRST 2000 ROWS ONLY",ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
			PreparedStatement pst_insert=targetConn.prepareStatement(INSERT);
			PreparedStatement pst_update=targetConn.prepareStatement(UPDATE);
			while(true){
				pst.setString(1, index);
				ResultSet rs=pst.executeQuery();
				int n=0;
				while(rs.next()){
					n++;
					
					String or_code=rs.getString("item_code");
					if(rs.isLast()){
						index=or_code;
					}
					boolean exist=checkExist(targetConn, or_code);
					//修改
					if(exist){
						pst_update.setInt(1, 2);
						pst_update.setDouble(2, 0d);
						pst_update.setDouble(3, 1d);
						pst_update.setDouble(4, rs.getDouble("unit_price"));
						pst_update.setString(5, rs.getString("item_name"));
						pst_update.setString(6, "收费项目");
						pst_update.setInt(7, 0);
						Date date=new Date();
						pst_update.setTimestamp(8, getTimestamp(date));
						pst_update.setTimestamp(9, getTimestamp(date));
						pst_update.setString(10, or_code);
						update(pst_update);
					}
					else{//新增  
						pst_insert.setString(1, or_code);
						pst_insert.setInt(2, 2);
						pst_insert.setDouble(3, 0d);
						pst_insert.setDouble(4, 1d);
						pst_insert.setDouble(5, rs.getDouble("unit_price"));
						pst_insert.setString(6, rs.getString("item_name"));
						pst_insert.setString(7, "收费项目");
						pst_insert.setInt(8, 0);
						Date date=new Date();
						pst_insert.setTimestamp(9, getTimestamp(date));
						pst_insert.setTimestamp(10, getTimestamp(date));
						insert(pst_insert);
					}
					
				}
				rs.close();
				if(n<2000)
					break;
			}
			
			
			pst.close();
			pst_insert.close();
			pst_update.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public void deleteData(Connection sourceConn, Connection targetConn,
			String name, String fromDate, String toDate) {
		// TODO Auto-generated method stub
		System.out.println("从："+fromDate+"   到："+toDate);
		
		Date from=null;
		Date to=null;

		try {
			from = format_s.parse(fromDate);
			to = format_s.parse(toDate);
		} catch (ParseException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try{
			EtlLog.progressOneTaskETLLog(targetConn, name, null);//启动日志
			PreparedStatement pst_delete=targetConn.prepareStatement(DELETE);
			
			pst_delete.executeUpdate();
			pst_delete.close();
				
//			EtlLog.finishOneTaskETLLog(targetConn, name, toDate);
		}
		catch(SQLException e){
			e.printStackTrace();
		}
		
	}
	
	public void updateData(Connection sourceConn, Connection targetConn,
			String name, String fromDate, String toDate) {

		System.out.println("从："+fromDate+"   到："+toDate);
		
		try {
			EtlLog.progressOneTaskETLLog(targetConn, name, null);//启动日志
			insertData(sourceConn, targetConn,name, fromDate, toDate);
//			EtlLog.finishOneTaskETLLog(targetConn, name, toDate);
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
	}

}
